﻿CREATE PROCEDURE [dbo].[GetAllProjectsWithMembership]
@UserId INT
AS
SELECT p.ProjectId,
	p.[Name],
	pm.StartDate,
	pm.EndDate,
	pm.HourlyRate,
	pm.UserId,
	CASE WHEN pm.ProjectId IS NOT NULL THEN 1 ELSE 0 END IsMember
FROM Projects p
	LEFT OUTER JOIN ProjectMembers pm ON p.ProjectId = pm.ProjectId AND pm.UserId = @UserId
ORDER BY IsMember DESC, StartDate DESC, EndDate DESC, HourlyRate DESC

